const pool = require("../db/conn");
const countControllers = require("../controllers/countControllers");

exports.selectList = async function (pageno,pagesize,sname,cname,major) {
    let sql =   " SELECT "+
                " 	rc_student.student_id,  "+
                " 	rc_student.student_number,  "+
                " 	rc_student.student_name,  "+
                " 	rc_student.student_email,  "+
                " 	rc_student.student_birthday,  "+
                " 	rc_student.student_sex,  "+
                " 	rc_student.student_last_login_time,  "+
                " 	rc_class.class_id,  "+
                " 	rc_class.class_name,  "+
                " 	rc_major.major_id,  "+
                " 	rc_major.major_name "+
                " FROM "+
                " 	rc_student "+
                " 	LEFT JOIN "+
                " 	rc_class "+
                " 	ON  "+
                " 		rc_student.student_class_id = rc_class.class_id "+
                " 	LEFT JOIN "+
                " 	rc_major "+
                " 	ON  "+
                " 		rc_class.class_major_id = rc_major.major_id "+
                " WHERE "+
                " 	rc_class.class_is_delete = 0  ";
    let params = [];
    if(sname!=""){
        sql += "AND rc_student.student_name like ? "
        params.push( `%${sname}%` );
    }
    if(cname!=""){
        sql += "AND rc_class.class_name like ? "
        params.push( `%${cname}%` );
    }
    if(major!=""){
        sql += "AND rc_major.major_name like ? "
        params.push( `%${major}%` );
    }    

    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}


exports.selectListByTeacherId = async function (tid,pageno,pagesize,cname,sname) {
    let sql =   " SELECT "+
                " 	rc_course.course_id,  "+
                " 	rc_course.course_name,  "+
                " 	rc_student.student_number,  "+
                " 	rc_student.student_id,  "+
                " 	rc_student.student_name,  "+
                " 	rc_student_course.sc_daily_score,  "+
                " 	rc_student_course.sc_exam_score,  "+
                " 	rc_student_course.sc_score,  "+
                " 	rc_student_course.sc_id "+
                " FROM "+
                " 	rc_course INNER JOIN rc_student_course "+
                " ON  "+
                " 	rc_course.course_id = rc_student_course.sc_course_id "+
                " LEFT JOIN rc_student "+
                " ON  "+
                " 	rc_student_course.sc_student_id = rc_student.student_id "+
                " WHERE "+
                " 	rc_course.course_is_delete = 0  AND rc_course.course_teacher_id = ? ";
    let params = [tid];
    
    if(cname!=null&&cname!=""){
        sql += "AND rc_course.course_name like ? "
        params.push( `%${cname}%` );
    }
    if(sname!=null&&sname!=""){
        sql += "AND rc_student.student_name like ? "
        params.push( `%${sname}%` );
    }   

    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}


exports.selectListByStudentId = async function (sid) {
    let sql =   " SELECT "+
                " 	rc_course.course_id,  "+
                " 	rc_course.course_teacher_id,  "+
                " 	rc_course.course_name,  "+
                " 	rc_course.course_grade,  "+
                " 	rc_course.course_time,  "+
                " 	rc_course.course_location,  "+
                " 	rc_course.course_credit,  "+
                " 	rc_course.course_selected_count,  "+
                " 	rc_course.course_max_size,  "+
                " 	rc_course.course_exam_date,  "+
                " 	rc_course.course_exam_location "+
                " FROM "+
                " 	rc_student_course "+
                " 	LEFT JOIN "+
                " 	rc_course "+
                " 	ON  "+
                " 		rc_student_course.sc_course_id = rc_course.course_id "+
                " WHERE "+
                " 	rc_student_course.sc_student_id = ? ";

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[sid]);
    conn.release();
    return result;
}

exports.selectInfoByStudentId = async function (sid) {
    let sql =   " SELECT "+
                " 	rc_student.student_id,  "+
                " 	rc_student.student_number,  "+
                " 	rc_student.student_name,  "+
                " 	rc_student.student_email,  "+
                " 	rc_student.student_birthday,  "+
                " 	rc_student.student_sex,  "+
                " 	rc_class.class_id,  "+
                " 	rc_class.class_grade,  "+
                " 	rc_class.class_name "+
                " FROM "+
                " 	rc_student "+
                " 	LEFT JOIN "+
                " 	rc_class "+
                " 	ON  "+
                " 		rc_student.student_class_id = rc_class.class_id "+
                " WHERE "+
                " 	rc_student.student_id = ? "
    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,[sid]);
    conn.release();
    return result;
}

exports.selectByNumber = async function (student_number,student_id) {
    let sql =   " SELECT"+
                " 	rc_student.student_id, "+
                " 	rc_student.student_class_id, "+
                " 	rc_student.student_number, "+
                " 	rc_student.student_name, "+
                " 	rc_student.student_email, "+
                " 	rc_student.student_birthday, "+
                " 	rc_student.student_sex, "+
                " 	rc_student.student_last_login_time, "+
                " 	rc_student.student_role_id"+
                " FROM"+
                " 	rc_student"+
                " WHERE "+
                " 	rc_student.student_number = ? ";

    let params = [ student_number ];

    if(student_id!=null&&student_id!=""){
        sql += "AND rc_student.student_id != ? "
        params.push( student_id );
    }   

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();
    
    return result[0];
}

exports.insert = async function (params) {
    let sql =  "insert into rc_student values (null,?,?,?,'123456',?,?,?,null,4)";
    
    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[  params.student_class_id,
                                                    params.student_number,
                                                    params.student_name,
                                                    params.student_email,
                                                    params.student_birthday,
                                                    params.student_sex
                                                ]);
    conn.release();
    
    return changedRows!=0;
}

exports.updateByDid = async function (params,student_id) {
    let sql =   " UPDATE "+
                " 	rc_student"+
                " SET "+
                " 	rc_student.student_class_id = ?, "+
                " 	rc_student.student_number = ?, "+
                " 	rc_student.student_name = ?, "+
                " 	rc_student.student_email = ?, "+
                " 	rc_student.student_birthday = ?, "+
                " 	rc_student.student_sex = ?, "+
                " 	rc_student.student_role_id = 4 "+
                " WHERE "+
                " 	rc_student.student_id = ? ";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[ params.student_class_id,
                                                   params.student_number,
                                                   params.student_name,
                                                   params.student_email,
                                                   params.student_birthday,
                                                   params.student_sex,
                                                   student_id
                                                ]);
    conn.release();
    
    return changedRows!=0;
}

exports.deleteByDid = async function (student_id) {
    let sql =  "DELETE FROM rc_student WHERE student_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[student_id]);
    conn.release();
    
    return changedRows!=0;
}

